-- 时间段内月份时间
SELECT to_char(ADD_MONTHS(TO_DATE('2017-05-13', 'yyyy-MM-dd'), ROWNUM - 1), 'yyyy"年"MM"月码洋"') AS month_header,
       CASE
       when trunc(ADD_MONTHS(TO_DATE('2017-05-13', 'yyyy-MM-dd'), ROWNUM - 1), 'MM') < TO_DATE('2017-05-13', 'yyyy-MM-dd')
         then TO_DATE('2017-05-13', 'yyyy-MM-dd')
       ELSE trunc(ADD_MONTHS(TO_DATE('2017-05-13', 'yyyy-MM-dd'), ROWNUM - 1), 'MM')
       end as begin,
       CASE
       WHEN last_day(trunc(ADD_MONTHS(TO_DATE('2017-05-13', 'yyyy-MM-dd'), ROWNUM - 1), 'MM')) > TO_DATE('2017-11-25', 'yyyy-MM-dd')
         THEN to_date('2017-11-25' || '23:59:59', 'yyyy-MM-dd HH24:mi:ss')
       ELSE to_date(to_char(last_day(trunc(ADD_MONTHS(TO_DATE('2017-05-13', 'yyyy-MM-dd'), ROWNUM - 1), 'MM')), 'yyyy-MM-dd') || '23:59:59', 'yyyy-MM-dd HH24:mi:ss')
       end as end
FROM DUAL
CONNECT BY ROWNUM <= months_between(to_date('2017-11-25', 'yyyy-MM-dd'), to_date('2017-05-13', 'yyyy-MM-dd')) + 1;


-- 时间段内周时间
SELECT to_char(to_DATE('2017-03-02', 'YYYY-MM-DD'), 'yyyy')
       || '年'
       || to_char(TRUNC(to_DATE('2017-03-02', 'YYYY-MM-DD') + (ROWNUM - 1) * 7, 'D') + 1, 'MM.dd')
       || '-'
       || to_char(TRUNC(to_DATE('2017-03-02', 'YYYY-MM-DD') + (ROWNUM - 1) * 7, 'D') + 7, 'MM.dd')
       || '码洋'
           as week_header,
       CASE
       WHEN TRUNC(to_DATE('2017-03-02', 'YYYY-MM-DD') + (ROWNUM - 1) * 7, 'D') + 1 < TO_DATE('2017-03-02', 'yyyy-MM-dd')
         THEN TO_DATE('2017-03-02', 'yyyy-MM-dd')
       ELSE TRUNC(to_DATE('2017-03-02', 'YYYY-MM-DD') + (ROWNUM - 1) * 7, 'D') + 1
       end
           AS begin,
       CASE
       WHEN TRUNC(to_DATE('2017-03-02', 'YYYY-MM-DD') + (ROWNUM-1) * 7, 'D') + 7 > to_DATE('2017-05-02', 'YYYY-MM-DD')
         THEN to_date('2017-05-02' || '23:59:59', 'yyyy-MM-dd HH24:mi:ss')
       ELSE to_date(to_char(TRUNC(to_DATE('2017-03-02', 'YYYY-MM-DD') + (ROWNUM-1) * 7, 'D') + 7, 'yyyy-MM-dd') || ' 23:59:59', 'yyyy-MM-dd HH24:mi:ss')
       end AS end
FROM DUAL
CONNECT BY ROWNUM <= TRUNC(to_DATE('2017-05-02', 'YYYY-MM-DD') - to_DATE('2017-03-02', 'YYYY-MM-DD')) / 7 + 2;


-- 时间段内年时间
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2014-10-19', 'yyyy-MM-dd'), (ROWNUM - 1) * 12), 'yyyy') || '年码洋' as year_header,
       CASE
       WHEN TRUNC(ADD_MONTHS(TO_DATE('2014-10-19', 'yyyy-MM-dd'), (ROWNUM - 1) * 12), 'yyyy') < TO_DATE('2014-10-19', 'yyyy-MM-dd')
         THEN TO_DATE('2014-10-19', 'yyyy-MM-dd')
       ELSE TRUNC(ADD_MONTHS(TO_DATE('2014-10-19', 'yyyy-MM-dd'), (ROWNUM - 1) * 12), 'yyyy')
       end
                                                                                                    AS begin,
       CASE
       WHEN ADD_MONTHS(TRUNC(ADD_MONTHS(TO_DATE('2014-10-19', 'yyyy-MM-dd'), (ROWNUM - 1) * 12),'YYYY'),12)-1 > to_DATE('2017-06-22', 'YYYY-MM-DD')
         THEN to_date('2017-06-22' || '23:59:59', 'yyyy-MM-dd HH24:mi:ss')
       ELSE to_date(to_char(ADD_MONTHS(TRUNC(ADD_MONTHS(TO_DATE('2014-10-19', 'yyyy-MM-dd'), (ROWNUM - 1) * 12),'YYYY'),12)-1, 'yyyy-MM-dd') || ' 23:59:59', 'yyyy-MM-dd HH24:mi:ss')
       end AS end
FROM DUAL
CONNECT BY ROWNUM <= months_between(to_date('2017-06-22', 'yyyy-MM-dd'), to_date('2014-10-19', 'yyyy-MM-dd')) / 12 + 2;




select * from (
                select * from (
                  select d.month_header, c.CLIENTS_NAME, p.H_NAME, p.H_PRICE, sum(i.TOTAL_MONEY_ITEM) total
                  from
                    (SELECT to_char(ADD_MONTHS(TO_DATE('2017-05-13', 'yyyy-MM-dd'), ROWNUM - 1), 'yyyy"年"MM"月码洋"') AS month_header,
                            CASE
                            when trunc(ADD_MONTHS(TO_DATE('2017-05-13', 'yyyy-MM-dd'), ROWNUM - 1), 'MM') < TO_DATE('2017-05-13', 'yyyy-MM-dd')
                              then TO_DATE('2017-05-13', 'yyyy-MM-dd')
                            ELSE trunc(ADD_MONTHS(TO_DATE('2017-05-13', 'yyyy-MM-dd'), ROWNUM - 1), 'MM')
                            end as begin,
                            CASE
                            WHEN last_day(trunc(ADD_MONTHS(TO_DATE('2017-05-13', 'yyyy-MM-dd'), ROWNUM - 1), 'MM')) > TO_DATE('2017-11-25', 'yyyy-MM-dd')
                              THEN to_date('2017-11-25' || '23:59:59', 'yyyy-MM-dd HH24:mi:ss')
                            ELSE to_date(to_char(last_day(trunc(ADD_MONTHS(TO_DATE('2017-05-13', 'yyyy-MM-dd'), ROWNUM - 1), 'MM')), 'yyyy-MM-dd') || '23:59:59', 'yyyy-MM-dd HH24:mi:ss')
                            end as end
                     FROM DUAL
                     CONNECT BY ROWNUM <= months_between(to_date('2017-11-25', 'yyyy-MM-dd'), to_date('2017-05-13', 'yyyy-MM-dd')) + 1) d,
                    FX_XS xs, FX_XS_ITEM i, BS_CLIENTS c, BS_PRODUCT p
                  where 1=1
                        and xs.INPUT_DATE BETWEEN d.begin and d.end
                        --         and xs.CLIENT_ID = 'KH00000020'
                        and xs.PX_ID = i.PX_ID
                        --         and i.H_ID = '010 7764 00032001520'
                        and xs.CLIENT_ID = c.CLIENTS_ID
                        and i.H_ID = p.H_ID
                  GROUP BY d.month_header, c.CLIENTS_NAME, p.H_NAME, p.H_PRICE
                  ORDER BY d.month_header
                ) PIVOT (sum(total) for (month_header) in ('2017年05月码洋','2017年06月码洋','2017年07月码洋','2017年08月码洋','2017年09月码洋'))
                -- ) PIVOT (sum(total) for (month_header) in (
                --     SELECT to_char(ADD_MONTHS(TO_DATE('2017-05-13', 'yyyy-MM-dd'), ROWNUM - 1), 'yyyy"年"MM"月码洋"')
                --     FROM DUAL
                --     CONNECT BY ROWNUM <= months_between(to_date('2017-11-25', 'yyyy-MM-dd'), to_date('2017-05-13', 'yyyy-MM-dd')) + 1
                --   ));
              ) t order by t.CLIENTS_NAME;

